﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ChicagoDealsN
{
    public class Deals
    {
        //private string strConnect = WebConfigurationManager.ConnectionStrings["localhost"].ConnectionString;
        private string strConnect = "Data Source=localhost\\sqlexpress;Initial Catalog=yvyas;Integrated Security=True";
        private string getConnection() { return strConnect; }

        public Boolean DLCreateDeal(String title, String imgPath, String companyName, String dealDescription, float orgPrice, float dealPrice, String startDate, String endDate, int qty, int userId)
        {
            //set is_avtive = false;
            SqlConnection conPubs;
            SqlCommand cmdInsert = new SqlCommand(); 
            string conn = getConnection();
            conPubs = new SqlConnection(conn);
            DateTime SubDate = DateTime.Now;

            try
            {
                conPubs.Open();
                cmdInsert = new SqlCommand("INSERT Deals (Deal_Title,Img_Path,Company_Name,Deal_Description,Org_Price,Deal_Price,Start_Date,End_Date,Quantity,User_Id,Is_Active,Submitted_Date) VALUES ( @dealTitle, @imgPath, @companyName, @dealDescription, @orgPrice, @dealPrice, @startDate, @endDate,@qty, @userId, @isActive,@Submitted_Date)", conPubs);
                cmdInsert.Parameters.Add("@dealTitle", title);
                cmdInsert.Parameters.Add("@imgPath", imgPath);
                cmdInsert.Parameters.Add("@companyName", companyName);
                cmdInsert.Parameters.Add("@dealDescription", dealDescription);
                cmdInsert.Parameters.Add("@orgPrice", SqlDbType.Float).Value = orgPrice;
                cmdInsert.Parameters.Add("@dealPrice", SqlDbType.Float).Value = dealPrice;
                cmdInsert.Parameters.Add("@startDate",SqlDbType.DateTime).Value = startDate;
                cmdInsert.Parameters.Add("@endDate", SqlDbType.DateTime).Value = endDate;
                cmdInsert.Parameters.Add("@qty", SqlDbType.Int).Value = qty;
                cmdInsert.Parameters.Add("@userId", SqlDbType.Int).Value = userId;
                cmdInsert.Parameters.Add("@isActive", SqlDbType.Bit).Value = 0;  //Setting is_active=false
                cmdInsert.Parameters.Add("@Submitted_Date", SqlDbType.DateTime).Value = SubDate;
                cmdInsert.ExecuteNonQuery();

                conPubs.Close();
            }
            catch (Exception e)
            {
                throw e;
            }

            return true;
        }

        public Boolean DLUpdateDeal(
            int id,
            String title,
            String companyName,
            String imgPath,
            String dealDescription,
            float orgPrice,
            float dealPrice,
            String startDate,
            String endDate,
            int qty
            )
        {
            SqlConnection conPubs;
            string conn = getConnection();
            conPubs = new SqlConnection(conn);
            SqlCommand cmdUpdate = new SqlCommand();
            try
            {
                conPubs.Open();
                cmdUpdate = new SqlCommand("UPDATE Deals SET Deal_Title=@Deal_Title_PARAMS, Company_Name=@Company_Name_PARAMS,Img_Path=@imgPath_PARAMS, Deal_Description=@Deal_Description_PARAMS, Org_Price=@Org_Price_PARAMS, Deal_Price=@Deal_Price_PARAMS, Start_Date=@Start_Date_PARAMS, End_Date=@End_Date_PARAMS, Quantity=@Quantity_PARAMS WHERE Deal_Id=@Deal_Id_PARAMS", conPubs);

                cmdUpdate.Parameters.AddWithValue("@Deal_Id_PARAMS", id);
                cmdUpdate.Parameters.AddWithValue("@Deal_Title_PARAMS", title);
                cmdUpdate.Parameters.AddWithValue("@Company_Name_PARAMS", companyName);
                cmdUpdate.Parameters.AddWithValue("@imgPath_PARAMS", imgPath);
                cmdUpdate.Parameters.AddWithValue("@Deal_Description_PARAMS", dealDescription);
                cmdUpdate.Parameters.AddWithValue("@Org_Price_PARAMS", orgPrice);
                cmdUpdate.Parameters.AddWithValue("@Deal_Price_PARAMS", dealPrice);
                cmdUpdate.Parameters.AddWithValue("@Start_Date_PARAMS", startDate);
                cmdUpdate.Parameters.AddWithValue("@End_Date_PARAMS", endDate);
                cmdUpdate.Parameters.AddWithValue("@Quantity_PARAMS", qty);
                

                cmdUpdate.ExecuteNonQuery();



            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conPubs.Close();
            }
            return true;        
        }

        public bool DLDeleteDeal(int id)
        {
            SqlConnection conPubs;
            SqlCommand cmdUpdate = new SqlCommand(); 
            string conn = getConnection();
            conPubs = new SqlConnection(conn);
            try
            {
                conPubs.Open();
                string strDelete = "UPDATE Deals SET Is_Active=0 WHERE Deal_Id=@id";
                cmdUpdate = new SqlCommand(strDelete, conPubs);
                cmdUpdate.Parameters.AddWithValue("@id", id);
                cmdUpdate.ExecuteNonQuery();
                cmdUpdate.Dispose();
                conPubs.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            return true;
        }
        // -----------------------------------------
        public DataSet DLGetRowById(int id)
        {
            SqlConnection conPubs;
            SqlCommand cmdSelect;

            string conn = getConnection();
            conPubs = new SqlConnection(conn);
            conPubs.Open();
            cmdSelect = new SqlCommand("SELECT * FROM Deals WHERE Deal_Id=" + id, conPubs);

            DataSet set = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmdSelect.CommandText, conPubs);
            da.Fill(set, "ITEMBYID");

            conPubs.Close();
            return set;

        }

        public DataSet DLGetDealById(int id)
        {
            SqlConnection conPubs;
            SqlCommand cmdSelect;

            string conn = getConnection();
            conPubs = new SqlConnection(conn);
            conPubs.Open();
            cmdSelect = new SqlCommand("SELECT * FROM Deals WHERE User_Id=" + id + "Order by Submitted_Date desc ", conPubs);

            DataSet set = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmdSelect.CommandText, conPubs);
            da.Fill(set, "ITEMBYID");

            conPubs.Close();
            return set;
        }


       
        
              public DataSet DLGetAllDeals()
              {
                  SqlConnection conPubs;
                  SqlCommand cmdGetAll ; 
                  string conn = getConnection();
                  conPubs = new SqlConnection(conn);
                  try
                  {
                      conPubs.Open();
                      cmdGetAll = new SqlCommand("SELECT * FROM DEALS WHERE Is_Active=1 AND (DATEDIFF(day, End_Date, GETDATE()) <= 0)");
                      DataSet set = new DataSet();
                      SqlDataAdapter da = new SqlDataAdapter(cmdGetAll.CommandText, conPubs);
                      da.Fill(set, "AllDealTable");
                      conPubs.Close();
                      return set;
                  }
                  catch (Exception e)
                  {
                      throw e;
                  }
                 
              }
         
    }  
}